//
//  DataCache.m
//  GameBoss
//
//  Created by dllo on 15/4/26.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "DataCache.h"
#import "ArticleListModel.h"
#import "PictureModel.h"
#import "VideoModel.h"

@implementation DataCache
+(instancetype)shareDataCache{
    static DataCache *dataCache = nil;
    static dispatch_once_t oneToTake;
    dispatch_once(&oneToTake, ^{
        dataCache = [[DataCache alloc] init];
    });
    return dataCache;
}

-(void)openDB{
    NSArray *sandArr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *sandBoxPath = [sandArr lastObject];
    
    //拼接一个数据库的路径,如果这个路径下没有数据库,则会创建一个数据库,并且打开这个数据库,如果有数据库了,这直接打开
    NSString *dbPath = [sandBoxPath stringByAppendingPathComponent:@"cache.sqlite"];
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"数据库开启成功");
        NSLog(@"%@", dbPath);
    }else{
        NSLog(@"数据库开启失败");
    }
}
-(void)createTable:(NSString *)tableName{
    NSString *sqlStr = @"";
    if ([tableName isEqualToString:@"latesTable"]) {
        sqlStr = @"create table if not exists latesTable(number integer primary key autoincrement, cmtCount integer, id text, image text, preView text, pubDate text, summary text, tags text, title text, url text)";
    }else if ([tableName isEqualToString:@"pictureTable"]){
        sqlStr = @"create table if not exists pictureTable(number integer primary key autoincrement, cover text, id text, name text, photoCount integer, url text)";
    }else if ([tableName isEqualToString:@"videoTable"]){
        sqlStr = @"create table if not exists videoTable(number integer primary key autoincrement, picUrl_448x252 text, pv text, title text, dataType integer, itemUrl text, iid text, picUrl_200x112 text, stripe_b_r text, picUrl text)";
    }
    //执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
    
}
-(void)insertModel:(NSString *)tableName model:(id)model{
    NSString *insertModel = @"";
    if ([tableName isEqualToString:@"latesTable"]) {
        insertModel = [NSString stringWithFormat:@"insert into latesTable(cmtCount, id, image, preView, pubDate, summary, tags, title, url) values('%ld', '%@', '%@', '%@', '%@', '%@', '%@', '%@',  '%@')",((ArticleListModel *)model).cmtCount, ((ArticleListModel *)model).modelId, ((ArticleListModel *)model).image, ((ArticleListModel *)model).preView, ((ArticleListModel *)model).pubDate, ((ArticleListModel *)model).summary, ((ArticleListModel *)model).tags, ((ArticleListModel *)model).title, ((ArticleListModel *)model).url];
    }else if ([tableName isEqualToString:@"pictureTable"]){
        insertModel = [NSString stringWithFormat:@"insert into pictureTable(cover, id, name, photoCount, url) values('%@', '%@', '%@', '%ld', '%@')",((PictureModel *)model).cover, ((PictureModel *)model).id, ((PictureModel *)model).name, ((PictureModel *)model).photoCount, ((PictureModel *)model).url];
    }else if ([tableName isEqualToString:@"videoTable"]){
        insertModel = [NSString stringWithFormat:@"insert into videoTable(picUrl_448x252, pv, title, dataType, itemUrl, iid, picUrl_200x112, stripe_b_r, picUrl) values('%@', '%@', '%@', '%ld', '%@', '%@', '%@', '%@', '%@')", ((VideoModel *)model).picUrl_448x252, ((VideoModel *)model).pv, ((VideoModel *)model).title, ((VideoModel *)model).dataType, ((VideoModel *)model).itemUrl, ((VideoModel *)model).iid, ((VideoModel *)model).picUrl_200x112, ((VideoModel *)model).stripe_b_r, ((VideoModel *)model).picUrl];
    }
    //执行sql语句
    sqlite3_exec(dbPoint, [insertModel UTF8String], nil, nil, nil);
}
-(NSMutableArray *)selectModel:(NSString *)tableName{
    //查询的逻辑
    //1.先从本地数据库中取出所有的数据
    //2.然后将取出的每条数据赋值给model
    //3.最后存入数组,返回给调用的类
    NSString *selectSQL = @"";
    if ([tableName isEqualToString:@"latesTable"]) {
        selectSQL = @"select * from latesTable";
    }else if ([tableName isEqualToString:@"pictureTable"]){
        selectSQL = @"select * from pictureTable";
    }else if ([tableName isEqualToString:@"videoTable"]){
        selectSQL = @"select * from videoTable";
    }
    //需要创建一个跟随指针,用来循环遍历数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    
    //第三个参数:查询语句字数限制,-1为不限制
    int result = sqlite3_prepare_v2(dbPoint, [selectSQL UTF8String], -1, &stmt, nil);
    //这个方法相等于数据库, sql,跟随指针关联在一起,一同完成查询操作
    
    //初始化 装model的大数组
    NSMutableArray *modelArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        //开始便利查询的数据库的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            if ([tableName isEqualToString:@"latesTable"]) {
#pragma mark  latesTable
                //让跟随指针以行进行查询遍历,如果没有行了,则停止循环
                //满足循环条件,则逐列取出内容
                int number = sqlite3_column_int(stmt, 0);
                int cmtCount = sqlite3_column_int(stmt, 1);
                const unsigned char *id = sqlite3_column_text(stmt, 2);
                const unsigned char *image = sqlite3_column_text(stmt, 3);
                const unsigned char *preView = sqlite3_column_text(stmt, 4);
                const unsigned char *pubDate = sqlite3_column_text(stmt, 5);
                const unsigned char *summary = sqlite3_column_text(stmt, 6);
                const unsigned char *tags = sqlite3_column_text(stmt, 7);
                const unsigned char *title = sqlite3_column_text(stmt, 8);
                const unsigned char *url = sqlite3_column_text(stmt, 9);

                //把取出来的内容进行类型转换
                NSInteger modelNumber = number;
                NSInteger modelCmtCount = cmtCount;
                NSString *modelId = [NSString stringWithUTF8String:(const char*)id];
                NSString *modelImage = [NSString stringWithUTF8String:(const char*)image];
                NSString *modelPreView = [NSString stringWithUTF8String:(const char*)preView];
                NSString *modelPubDate = [NSString stringWithUTF8String:(const char*)pubDate];
                NSString *modelSummary = [NSString stringWithUTF8String:(const char*)summary];
                NSString *modelTags = [NSString stringWithUTF8String:(const char*)tags];
                NSString *modelTitle = [NSString stringWithUTF8String:(const char*)title];
                NSString *modelUrl = [NSString stringWithUTF8String:(const char*)url];

                ArticleListModel *model = [[ArticleListModel alloc] init];
                model.number = modelNumber;
                model.cmtCount = modelCmtCount;
                model.modelId = modelId;
                model.image = modelImage;
                model.preView = modelPreView;
                model.pubDate = modelPubDate;
                model.summary = modelSummary;
                model.tags = modelTags;
                model.title = modelTitle;
                model.url = modelUrl;
                [modelArr addObject:model];
                [model release];

            }else if ([tableName isEqualToString:@"pictureTable"]){
#pragma mark  pictureTable
                //让跟随指针以行进行查询遍历,如果没有行了,则停止循环
                //满足循环条件,则逐列取出内容
                int number = sqlite3_column_int(stmt, 0);
                const unsigned char *id = sqlite3_column_text(stmt, 1);
                int photoCount = sqlite3_column_int(stmt, 2);
                const unsigned char *cover = sqlite3_column_text(stmt, 3);
                const unsigned char *name = sqlite3_column_text(stmt, 4);
                const unsigned char *url = sqlite3_column_text(stmt, 5);
                
                //把取出来的内容进行类型转换
                NSInteger modelNumber = number;
                NSInteger modelPhotoCount = photoCount;
                NSString *modelId = [NSString stringWithUTF8String:(const char*)id];
                NSString *modelCover = [NSString stringWithUTF8String:(const char*)cover];
                NSString *modelName = [NSString stringWithUTF8String:(const char*)name];
                NSString *modelUrl = [NSString stringWithUTF8String:(const char*)url];
                
                PictureModel *model = [[PictureModel alloc] init];
                model.number = modelNumber;
                model.cover = modelCover;
                model.modelId = modelId;
                model.name = modelName;
                model.photoCount = modelPhotoCount;
                model.url = modelUrl;
                [modelArr addObject:model];
                [model release];

            }else if ([tableName isEqualToString:@"videoTable"]){
#pragma mark  videoTable
                //让跟随指针以行进行查询遍历,如果没有行了,则停止循环
                //满足循环条件,则逐列取出内容
                int number = sqlite3_column_int(stmt, 0);
                int dataType = sqlite3_column_int(stmt, 1);
                const unsigned char *picUrl_448x252 = sqlite3_column_text(stmt, 2);
                const unsigned char *pv = sqlite3_column_text(stmt, 3);
                const unsigned char *title = sqlite3_column_text(stmt, 4);
                const unsigned char *itemUrl = sqlite3_column_text(stmt, 5);
                const unsigned char *iid = sqlite3_column_text(stmt, 6);
                const unsigned char *picUrl_200x112 = sqlite3_column_text(stmt, 7);
                const unsigned char *stripe_b_r = sqlite3_column_text(stmt, 8);
                const unsigned char *picUrl = sqlite3_column_text(stmt, 9);
                
                //把取出来的内容进行类型转换
                NSInteger modelNumber = number;
                NSInteger modelDataType = dataType;
                NSString *modelPicUrl_448x252 = [NSString stringWithUTF8String:(const char*)picUrl_448x252];
                NSString *modelPv = [NSString stringWithUTF8String:(const char*)pv];
                NSString *modelTitle = [NSString stringWithUTF8String:(const char*)title];
                NSString *modelItemUrl = [NSString stringWithUTF8String:(const char*)itemUrl];
                NSString *modelIid = [NSString stringWithUTF8String:(const char*)iid];
                NSString *modelPicUrl_200x112 = [NSString stringWithUTF8String:(const char*)picUrl_200x112];
                NSString *modelStripe_b_r = [NSString stringWithUTF8String:(const char*)stripe_b_r];
                NSString *modelpicUrl = [NSString stringWithUTF8String:(const char*)picUrl];
                
                VideoModel *model = [[VideoModel alloc] init];
                model.number = modelNumber;
                model.dataType = modelDataType;
                model.picUrl_448x252 = modelPicUrl_448x252;
                model.pv = modelPv;
                model.title = modelTitle;
                model.itemUrl = modelItemUrl;
                model.iid = modelIid;
                model.picUrl_200x112 = modelPicUrl_200x112;
                model.stripe_b_r = modelStripe_b_r;
                model.picUrl = modelpicUrl;
                [modelArr addObject:model];
                [model release];
                
            }
        }
    }else{
        NSLog(@"查询失败");
    }
    return modelArr;
}
-(void)dropTable:(NSString *)tableName{
    NSString *dropTable = @"";
    if ([tableName isEqualToString:@"latesTable"]) {
        dropTable = @"drop table latesTable";
    }else if ([tableName isEqualToString:@"pictureTable"]){
        dropTable = @"drop table pictureTable";
    }else if ([tableName isEqualToString:@"videoTable"]){
        dropTable = @"drop table videoTable";
    }
    int result = sqlite3_exec(dbPoint, [dropTable UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");
    }
}
-(void)closeDB{
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功");
    }
}
@end
